package vietnam.traveltools.variables;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.SQLException;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class MySQLiteOpenHelper extends SQLiteOpenHelper {
	private static final String db_name = "vntt.db";
	private String TAG = "TAG";
	private static final int vs = 3;
	Context myContext;

	private SQLiteDatabase database;
	private static String path = "/data/data/vietnam.traveltools/databases/";

	// Table ATM
	public static final String tb_name_atm = "atm";
	public static final String cl_id_atm = "_id";
	public static final String cl_atm_bank = "_bank";
	public static final String cl_atm_branch = "_branch";
	public static final String cl_atm_address = "_address";
	public static final String cl_atm_time = "_time";
	public static final String cl_atm_city = "_city";

	// Table FOOD
	public static final String tb_name_food = "food";
	public static final String cl_id_food = "_id";
	public static final String cl_food_time_job = "_time_job";
	public static final String cl_food_name = "_name";
	public static final String cl_food_address = "_address";
	public static final String cl_food_description = "_description";
	public static final String cl_food_type = "_type";
	public static final String cl_food_price = "_price";
	public static final String cl_food_city = "_city";

	// Table HOTEL
	public static final String tb_name_hotel = "hotel";
	public static final String cl_id_hotel = "_id";
	public static final String cl_hotel_name = "_name";
	public static final String cl_hotel_address = "_address";
	public static final String cl_hotel_star = "_star";
	public static final String cl_hotel_phone = "_phone";
	public static final String cl_hotel_fax = "_fax";
	public static final String cl_hotel_price = "_price";
	public static final String cl_hotel_city = "_city";

	// Table TAXI
	public static final String tb_name_taxi = "taxi";
	public static final String cl_id_taxi = "_id";
	public static final String cl_taxi_name = "_name";
	public static final String cl_taxi_phone = "_phone";
	public static final String cl_taxi_price_open = "_price_open";
	public static final String cl_taxi_price_1 = "_price_1";
	public static final String cl_taxi_price_2 = "_price_2";
	public static final String cl_taxi_city = "_city";

	// Table TRAVEL
	public static final String tb_name_travel = "travel";
	public static final String cl_id_travel = "_id";
	public static final String cl_travel_name = "_name";
	public static final String cl_travel_address = "_address";
	public static final String cl_travel_description = "_description";
	public static final String cl_travel_price = "_price";
	public static final String cl_travel_city = "_city";

	public MySQLiteOpenHelper(Context context) {

		super(context, db_name, null, vs);
		// database = this.getWritableDatabase();
		this.myContext = context;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

	}

	public Cursor SELECTSQL(String sql) {
		return database.rawQuery(sql, null);
	}

	public synchronized void closeBD() {
		if (database != null && database.isOpen())
			database.close();
	}

	public void openDB() throws SQLException {
		String myPath = path + db_name;
		database = SQLiteDatabase.openDatabase(myPath, null,
				SQLiteDatabase.OPEN_READWRITE);
	}

	public void checkAndcopyDB() {
		boolean dbExist = checkDB();
		if (dbExist) {
			Log.d(TAG, "Database Already Exist!");
		} else {
			this.getReadableDatabase();
			try {
				copyDB();
			} catch (IOException e) {
				Log.d(TAG, "Error copying database");
			}
		}
	}

	private boolean checkDB() {
		SQLiteDatabase checkDB = null;
		try {
			String myPath = path + db_name;
			checkDB = SQLiteDatabase.openDatabase(myPath, null,
					SQLiteDatabase.OPEN_READWRITE);
		} catch (SQLiteException e) {
		}
		if (checkDB != null)
			checkDB.close();
		return checkDB != null ? true : false;
	}

	private void copyDB() throws IOException {
		InputStream myInput = myContext.getAssets().open(db_name);
		String outFileName = path + db_name;
		OutputStream myOutput = new FileOutputStream(outFileName);
		byte[] buffer = new byte[1024];
		int length;
		while ((length = myInput.read(buffer)) > 0) {
			myOutput.write(buffer, 0, length);
		}
		myOutput.flush();
		myOutput.close();
		myInput.close();
	}

	public void ExeSQLData(String sql) throws SQLException {
		database.execSQL(sql);
	}

	public Cursor QueryData(String query) throws SQLException {
		return database.rawQuery(query, null);
	}
	// ////////////////////////////////////////////////////

	

	// /////////Tạo database
	// String sqlCreatatm = "CREATE TABLE " + tb_name + "(" + cl_id_atm
	// + " INTEGER PRIMARY KEY AUTOINCREMENT, "+ cl_bank + " VARCHAR(50), "
	// + cl_branch + " VARCHAR(50), "
	// + cl_address + " TEXT, " + cl_time + " INTEGER," + cl_city +
	// " VARCHAR(30))";
	//
	// // cac ban nen log cac cau sql ra va chay thu tren sqliteadmin de
	// phat hien ra loi
	// Log.d("debug", sqlCreatatm);
	// db.execSQL(sqlCreatatm);
	//
	// // neu csdl cp nhieu bang thi cac bang khac o day
	//
	// // ...
	// // cac phuong thuc de lam viec voi bang tb_name
	//
	// // insert vao bang tb_name = "atm"
	// public void INSERT_atm(VariablesDatabase table_atm) {
	// ContentValues values = new ContentValues();
	// values.put(cl_bank, table_atm.get_bank());
	// values.put(cl_branch, table_atm.get_branch());
	// values.put(cl_address, table_atm.get_address());
	// values.put(cl_time, table_atm.get_time());
	// values.put(cl_city, table_atm.get_city());
	// database.insertOrThrow(tb_name, null, values);
	// }
	//
	// // delete 1 dong trong bang tb_name = "atm" voi gia tri cot id = id
	// public void DELETE_atm(int id) {
	// database.delete(tb_name, cl_id_atm + " = " + id, null);
	// }
	//
	// // update 1 dong trong bang tb_name = "atm" voi gia tri cot id = id
	// public void UPDATE_atm(VariablesDatabase table_atm, int id) {
	// ContentValues values = new ContentValues();
	// values.put(cl_bank, table_atm.get_bank());
	// values.put(cl_branch, table_atm.get_branch());
	// values.put(cl_address, table_atm.get_address());
	// values.put(cl_time, table_atm.get_time());
	// values.put(cl_city, table_atm.get_city());
	// database.update(tb_name, values, cl_id_atm + " = " + id, null);
	// }
	//
	// // select toan bo bang tb_name = "atm"
	// public Cursor SELECT_ALL__atm() {
	// return database.query(tb_name, null, null, null, null, null, null);
	// }
	//
	// // neu DB cua cac ban co nhieu bang cac ban co the viet them cac
	// // phuong thuc update,insert,delete,select cho cac bang nay tiep o ben
	// duoi
	//
	// // .....
	//
	// // minh viet phuong thuc chung dung de SELECT trong CSDL db_name =
	// // "MyDataBase.db"
	// // khi truyen vao 1 cau select sql voi 1 bang bat ky trong DB thi ket qua
	// // tra ra la cursor
	// //////////////////////////////////////////////////////////////////////

	// // public void CopyDB(InputStream inputStr, OutputStream outputStr)
	// throws
	// // IOException {
	// // byte[] buffer = new byte[1024];
	// // int lenght;
	// // while ((lenght = inputStr.read(buffer)) > 0) {
	// // outputStr.write(buffer, 0, lenght);
	// // }
	// // outputStr.flush();
	// // outputStr.close();
	// // inputStr.close();
	// //
	// // }
	// //
	// /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

}
